import { Pool } from 'pg';

const pool = new Pool({
  connectionString: process.env.DATABASE_URL
});

export async function runNotificationMigration() {
  const client = await pool.connect();
  
  try {
    await client.query('BEGIN');
    
    console.log('🚀 开始通知系统数据库迁移...');

    // 1. 创建通知设置表
    await client.query(`
      CREATE TABLE IF NOT EXISTS notification_settings (
        id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
        user_id VARCHAR(255) NOT NULL REFERENCES users(id) ON DELETE CASCADE,
        task_reminders BOOLEAN DEFAULT true,
        daily_summary BOOLEAN DEFAULT true,
        weekly_review BOOLEAN DEFAULT true,
        overdue_alerts BOOLEAN DEFAULT true,
        reminder_time TIME DEFAULT '09:00',
        sound_enabled BOOLEAN DEFAULT true,
        vibration_enabled BOOLEAN DEFAULT true,
        email_notifications BOOLEAN DEFAULT false,
        created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
        updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
        UNIQUE(user_id)
      )
    `);

    console.log('✅ 通知设置表创建成功');

    // 2. 创建通知历史表
    await client.query(`
      CREATE TABLE IF NOT EXISTS notification_history (
        id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
        user_id VARCHAR(255) NOT NULL REFERENCES users(id) ON DELETE CASCADE,
        type VARCHAR(50) NOT NULL, -- 'reminder', 'overdue', 'summary', 'deadline'
        title VARCHAR(255) NOT NULL,
        message TEXT NOT NULL,
        task_id TEXT REFERENCES tasks(id) ON DELETE SET NULL,
        sent_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
        is_read BOOLEAN DEFAULT false,
        read_at TIMESTAMP WITH TIME ZONE,
        delivery_status VARCHAR(20) DEFAULT 'sent', -- 'sent', 'failed', 'pending'
        created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
      )
    `);

    console.log('✅ 通知历史表创建成功');

    // 3. 创建通知队列表（用于延迟发送）
    await client.query(`
      CREATE TABLE IF NOT EXISTS notification_queue (
        id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
        user_id VARCHAR(255) NOT NULL REFERENCES users(id) ON DELETE CASCADE,
        type VARCHAR(50) NOT NULL,
        title VARCHAR(255) NOT NULL,
        message TEXT NOT NULL,
        task_id TEXT REFERENCES tasks(id) ON DELETE CASCADE,
        scheduled_at TIMESTAMP WITH TIME ZONE NOT NULL,
        status VARCHAR(20) DEFAULT 'pending', -- 'pending', 'sent', 'failed', 'cancelled'
        attempts INTEGER DEFAULT 0,
        max_attempts INTEGER DEFAULT 3,
        last_attempt_at TIMESTAMP WITH TIME ZONE,
        error_message TEXT,
        created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
        updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
      )
    `);

    console.log('✅ 通知队列表创建成功');

    // 4. 创建索引以提高查询性能
    await client.query(`
      CREATE INDEX IF NOT EXISTS idx_notification_settings_user_id 
      ON notification_settings(user_id)
    `);

    await client.query(`
      CREATE INDEX IF NOT EXISTS idx_notification_history_user_id 
      ON notification_history(user_id)
    `);

    await client.query(`
      CREATE INDEX IF NOT EXISTS idx_notification_history_sent_at 
      ON notification_history(sent_at DESC)
    `);

    await client.query(`
      CREATE INDEX IF NOT EXISTS idx_notification_history_type 
      ON notification_history(type)
    `);

    await client.query(`
      CREATE INDEX IF NOT EXISTS idx_notification_history_task_id 
      ON notification_history(task_id)
    `);

    await client.query(`
      CREATE INDEX IF NOT EXISTS idx_notification_queue_user_id 
      ON notification_queue(user_id)
    `);

    await client.query(`
      CREATE INDEX IF NOT EXISTS idx_notification_queue_scheduled_at 
      ON notification_queue(scheduled_at)
    `);

    await client.query(`
      CREATE INDEX IF NOT EXISTS idx_notification_queue_status 
      ON notification_queue(status)
    `);

    console.log('✅ 索引创建成功');

    // 5. 创建触发器以自动更新 updated_at 字段
    await client.query(`
      CREATE OR REPLACE FUNCTION update_notification_updated_at()
      RETURNS TRIGGER AS $$
      BEGIN
        NEW.updated_at = NOW();
        RETURN NEW;
      END;
      $$ LANGUAGE plpgsql;
    `);

    await client.query(`
      DROP TRIGGER IF EXISTS trigger_notification_settings_updated_at 
      ON notification_settings
    `);

    await client.query(`
      CREATE TRIGGER trigger_notification_settings_updated_at
        BEFORE UPDATE ON notification_settings
        FOR EACH ROW
        EXECUTE FUNCTION update_notification_updated_at()
    `);

    await client.query(`
      DROP TRIGGER IF EXISTS trigger_notification_queue_updated_at 
      ON notification_queue
    `);

    await client.query(`
      CREATE TRIGGER trigger_notification_queue_updated_at
        BEFORE UPDATE ON notification_queue
        FOR EACH ROW
        EXECUTE FUNCTION update_notification_updated_at()
    `);

    console.log('✅ 触发器创建成功');

    // 6. 为现有用户创建默认通知设置
    const existingUsersResult = await client.query(`
      SELECT id FROM users 
      WHERE id NOT IN (SELECT user_id FROM notification_settings)
    `);

    if (existingUsersResult.rows.length > 0) {
      console.log(`📝 为 ${existingUsersResult.rows.length} 个现有用户创建默认通知设置...`);
      
      for (const user of existingUsersResult.rows) {
        await client.query(`
          INSERT INTO notification_settings (
            user_id, task_reminders, daily_summary, weekly_review, 
            overdue_alerts, reminder_time, sound_enabled, 
            vibration_enabled, email_notifications
          ) VALUES ($1, true, true, true, true, '09:00', true, true, false)
        `, [user.id]);
      }
      
      console.log('✅ 默认通知设置创建完成');
    }

    await client.query('COMMIT');
    console.log('🎉 通知系统数据库迁移完成！');

  } catch (error) {
    await client.query('ROLLBACK');
    console.error('❌ 通知系统数据库迁移失败:', error);
    throw error;
  } finally {
    client.release();
  }
}

// 如果直接运行此文件，执行迁移
if (process.argv[1]?.includes('notificationMigration')) {
  runNotificationMigration()
    .then(() => {
      console.log('迁移执行完成');
      process.exit(0);
    })
    .catch((error) => {
      console.error('迁移执行失败:', error);
      process.exit(1);
    });
}
